package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.BaseDao;
import dao.ChangeDao;
import entity.User;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChangeDaoImpl extends BaseDao implements ChangeDao {

    @Override
    public int show(String password, int user_id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;

        try {

            String sql = "UPDATE `users` SET `password` = ? WHERE `user_id` = ?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, password);
            statement.setInt(2, user_id);
            return statement.executeUpdate();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> show1(String role) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        List<User> list = new ArrayList<>();

        try {

            String sql = "SELECT username, sex, phone, role FROM `users` WHERE role=?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, role);
            set = statement.executeQuery();

            while (set.next()) {
                User user = new User();
                user.setUsername(set.getString("username"));
                user.setSex(set.getString("sex"));
                user.setPhone(set.getString("phone"));
                user.setRole(set.getString("role"));
                list.add(user);
            }
            return list;
        } finally {
            if (set != null) {
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> show2(String username, String newRole1) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        List<User> list = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder("SELECT user_id, username, sex, phone, role FROM users WHERE role!='admin' ");
            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append("AND username = ? ");
            }
            if (!StringUtils.isNullOrEmpty(newRole1)) {
                sql.append("AND role = ? ");
            }

            statement = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (!StringUtils.isNullOrEmpty(username)) {
                statement.setString(paramIndex++, username);
            }
            if (!StringUtils.isNullOrEmpty(newRole1)) {
                statement.setString(paramIndex, newRole1);
            }

            set = statement.executeQuery();
            while (set.next()) {
                User user = new User();
                user.setUser_id(set.getInt("user_id"));
                user.setUsername(set.getString("username"));
                user.setSex(set.getString("sex"));
                user.setPhone(set.getString("phone"));
                user.setRole(set.getString("role"));
                list.add(user);
            }
            return list;
        } finally {
            if (set != null) {
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> getAllUsers() throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;
        ResultSet set = null;
        List<User> list = new ArrayList<>();

        try {

            String sql = "SELECT user_id, username, sex, phone, role FROM users WHERE role='student' OR role='dormitory'";
            statement = conn.prepareStatement(sql);
            set = statement.executeQuery();

            while (set.next()) {
                User user = new User();
                user.setUser_id(set.getInt("user_id"));
                user.setUsername(set.getString("username"));
                user.setSex(set.getString("sex"));
                user.setPhone(set.getString("phone"));
                user.setRole(set.getString("role"));
                list.add(user);
            }
            return list;
        } finally {
            if (set != null) {
                try {
                    set.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public int show3(String role, int user_id) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;

        try {

            String sql = "UPDATE `users` SET `role` = ? WHERE `user_id` = ?";
            statement = conn.prepareStatement(sql);
            statement.setString(1, role);
            statement.setInt(2, user_id);
            return statement.executeUpdate();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public int getTotalCount(String username, String role) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM users WHERE role!='admin' ");
            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append("AND username LIKE ? ");
            }
            if (!StringUtils.isNullOrEmpty(role)) {
                sql.append("AND role = ? ");
            }

            statement = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (!StringUtils.isNullOrEmpty(username)) {
                statement.setString(paramIndex++, "%" + username + "%");
            }
            if (!StringUtils.isNullOrEmpty(role)) {
                statement.setString(paramIndex, role);
            }

            rs = statement.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public List<User> getUsersByPage(String username, String role, int page, int pageSize) throws SQLException, ClassNotFoundException {
       getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        List<User> list = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder("SELECT user_id, username, sex, phone, role FROM users WHERE role!='admin' ");
            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append("AND username LIKE ? ");
            }
            if (!StringUtils.isNullOrEmpty(role)) {
                sql.append("AND role = ? ");
            }
            sql.append("ORDER BY user_id ASC LIMIT ?, ?");

            statement = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (!StringUtils.isNullOrEmpty(username)) {
                statement.setString(paramIndex++, "%" + username + "%");
            }
            if (!StringUtils.isNullOrEmpty(role)) {
                statement.setString(paramIndex++, role);
            }
            statement.setInt(paramIndex++, (page - 1) * pageSize);
            statement.setInt(paramIndex, pageSize);

            rs = statement.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUser_id(rs.getInt("user_id"));
                user.setUsername(rs.getString("username"));
                user.setSex(rs.getString("sex"));
                user.setPhone(rs.getString("phone"));
                user.setRole(rs.getString("role"));
                list.add(user);
            }
            return list;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}